package dbhelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import entities.Comment;

public class Comments {
	public static final String tableName = " ebaydb.comments ";
	private static final String postCommentQuerySQL = "INSERT INTO" + tableName
			+ "(text,item_id,date,user_id) VALUES (?,?,?,?);";
	private static final String getCommentsSQLQuery=
	"SELECT comments.text, comments.date, users.user_name FROM" + tableName + ",ebaydb.users " + "where item_id=?" +
			" AND users.id=comments.user_id   ORDER BY date DESC LIMIT ?,?";
	private static final String getCommentsStartFromTimeQuery = 
			getCommentsSQLQuery.replace("   ", " AND comments.date > ? ");
	private static final int COMMENTS_PER_PAGE = 10;
	
	public static boolean postNewComment(Comment comment) {
		Connection connection = null;
		PreparedStatement  postCommentPS = null;
		try {
			connection = MyDBConnectionPool.getConnection();
			postCommentPS = connection.prepareStatement(postCommentQuerySQL);
			postCommentPS.setString(1, comment.getText());
			postCommentPS.setLong(2, comment.getItemID());
			postCommentPS.setTimestamp(3, comment.getDate());
			postCommentPS.setLong(4, comment.getUserID());
			return postCommentPS.executeUpdate() == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (postCommentPS != null)
					postCommentPS.close();
				if(connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;

	}
	
	public static List<Comment> getComments(long itemID,Timestamp startFrom,int pageNumber) {
		List<Comment> result = new ArrayList<Comment>();
		PreparedStatement  getCommentsPS = null;
		Connection connection = null;
		String query = (startFrom == null) ? getCommentsSQLQuery : getCommentsStartFromTimeQuery;
		int injectedParametersCounter = 1 ;
		try {
			connection =  MyDBConnectionPool.getConnection();
			getCommentsPS = connection.prepareStatement(query);
			getCommentsPS.setLong(injectedParametersCounter++, itemID);
			if(startFrom != null)
				getCommentsPS.setTimestamp(injectedParametersCounter++, startFrom);
			int pageOffset = COMMENTS_PER_PAGE * pageNumber;
			getCommentsPS.setInt(injectedParametersCounter++, pageOffset);
			getCommentsPS.setInt(injectedParametersCounter++, pageOffset + COMMENTS_PER_PAGE);
			populateComments(
			getCommentsPS.executeQuery(),result
			);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (getCommentsPS != null)
					getCommentsPS.close();
				if(connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;

	}

	private static void populateComments(ResultSet rs,
			List<Comment> result)  {
		try {
			while(rs.next()){
				Comment comment = new Comment();
				comment.setDate(rs.getTimestamp("date"));
				comment.setText(rs.getString("text"));
				comment.setUserDisplayName(rs.getString("user_name"));
				result.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		}
}
